
package com.borqs.music.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.borqs.music.util.MyLogger;

/**
 * The helper class for creating/updating db and tables.
 */
public class MusicDatabaseHelper extends SQLiteOpenHelper {
    static MyLogger logger = MyLogger.getLogger("MusicDatabaseHelper");
    private static final String DATABASE_NAME = "music.db";

    private static final int DATABASE_VERSION = 7;

    public MusicDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        logger.d("onCreate Database.");

        db.execSQL("CREATE TABLE " + AudioTraceInfo.NAME + "(" +
                AudioTraceInfo.COLUMN_ID + " INTEGER PRIMARY KEY, " +
                AudioTraceInfo.COLUMN_SONG_ID + " INTEGER, " +
                AudioTraceInfo.COLUMN_SIZE + " INTEGER, " +
                AudioTraceInfo.COLUMN_DATE_ADDED + " INTEGER, " +
                AudioTraceInfo.COLUMN_DATA + " TEXT, " +
                AudioTraceInfo.COLUMN_MD5 + " TEXT, " +
                AudioTraceInfo.COLUMN_IS_FAVORITE + " INTEGER, " +
                AudioTraceInfo.COLUMN_IS_UPLOADED_SONG + " INTEGER, " +
                AudioTraceInfo.COLUMN_IS_UPLOADED_LYRIC + " INTEGER);");

        db.execSQL("CREATE TABLE " + UploadInfo.NAME + "(" +
                UploadInfo.COLUMN_ID + " INTEGER PRIMARY KEY, " +
                UploadInfo.COLUMN_SONG_ID + " INTEGER, " +
                UploadInfo.COLUMN_SIZE + " INTEGER, " +
                UploadInfo.COLUMN_DATE_ADDED + " INTEGER, " +
                UploadInfo.COLUMN_SDCARD_ID + " INTEGER);");

        db.execSQL("CREATE TABLE " + SongCacheInfo.NAME + "(" +
                SongCacheInfo.COLUMN_ID + " INTEGER PRIMARY KEY, " +
                SongCacheInfo.COLUMN_SONG_ID + " INTEGER, " +

                SongCacheInfo.COLUMN_FILE_NAME + " TEXT, " +
                SongCacheInfo.COLUMN_FILE_TYPE + " TEXT, " +
                SongCacheInfo.COLUMN_FILE_URL + " TEXT, " +
                SongCacheInfo.COLUMN_FILE_SIZE + " INTEGER, " +
                SongCacheInfo.COLUMN_FILE_MD5 + " TEXT, " +

                SongCacheInfo.COLUMN_TITLE + " TEXT, " +
                SongCacheInfo.COLUMN_ARTIST + " TEXT, " +
                SongCacheInfo.COLUMN_ALBUM + " TEXT, " +
                SongCacheInfo.COLUMN_YEAR + " TEXT, " +
                SongCacheInfo.COLUMN_GENRE + " TEXT, " +
                SongCacheInfo.COLUMN_LYRICS + " TEXT, " +
                SongCacheInfo.COLUMN_IMG_URL + " TEXT, " +

                SongCacheInfo.COLUMN_IS_FAVORITE + " INTEGER, " +
                SongCacheInfo.COLUMN_IS_NOT_IN_DEVICE + " INTEGER, " +
                SongCacheInfo.COLUMN_IS_PUBLIC_SONG + " INTEGER, " +

                SongCacheInfo.COLUMN_BITRATE + " INTEGER, " +
                SongCacheInfo.COLUMN_DURATION + " INTEGER, " +

                SongCacheInfo.COLUMN_CACHE_TYPE + " INTEGER);");

        db.execSQL("CREATE TABLE " + SingerCacheInfo.NAME + "(" +
                SingerCacheInfo.COLUMN_ID + " INTEGER PRIMARY KEY, " +
                SingerCacheInfo.COLUMN_SINGER_ID + " INTEGER, " +

                SingerCacheInfo.COLUMN_SINGER_NAME + " TEXT, " +
                SingerCacheInfo.COLUMN_SINGER_IMG + " TEXT, " +
                SingerCacheInfo.COLUMN_COUNT + " INTEGER, " +

                SingerCacheInfo.COLUMN_CACHE_TYPE + " INTEGER);");

        db.execSQL("CREATE TABLE " + StatisticsInfo.NAME + "(" +
                StatisticsInfo.COLUMN_ID + " INTEGER PRIMARY KEY, " +
                StatisticsInfo.COLUMN_SONG_MD5 + " TEXT, " +
                StatisticsInfo.COLUMN_LYRIC_MD5 + " TEXT, " +
                StatisticsInfo.COLUMN_LISTEN_COUNT + " INTEGER, " +
                StatisticsInfo.COLUMN_LYRIC_CORRECT + " INTEGER);");
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        logger.d("Upgrading database from version #" + oldVersion + " to #" + newVersion
                + " ;This will destroy all old data.");
        String sql = "DROP TABLE IF EXISTS %s;";
        db.execSQL(String.format(sql, AudioTraceInfo.NAME));
        db.execSQL(String.format(sql, UploadInfo.NAME));
        db.execSQL(String.format(sql, SongCacheInfo.NAME));
        db.execSQL(String.format(sql, SingerCacheInfo.NAME));
        db.execSQL(String.format(sql, StatisticsInfo.NAME));
        onCreate(db);
    }
}
